const express = require('express');
const router = express.Router();
const db = require('../config/database');
const { intelligentOrderMatching } = require('../utils/orderMatching');
const { calculateDeliveryFee, getCurrentTimeSlot } = require('../utils/feeCalculator');

// 确保核心表存在（最小可用结构），避免因缺表/缺列导致 500
let orderTableColumnsCache = null;

async function ensureOrderUsersColumns() {
    try {
        const [cols] = await db.execute('SHOW COLUMNS FROM order_users');
        const has = (name) => cols.some((c) => c.Field === name);

        const defs = [
            { name: 'credit_score', sql: 'ALTER TABLE order_users ADD COLUMN credit_score INT DEFAULT 100' },
            { name: 'total_orders', sql: 'ALTER TABLE order_users ADD COLUMN total_orders INT DEFAULT 0' },
            { name: 'completed_orders', sql: 'ALTER TABLE order_users ADD COLUMN completed_orders INT DEFAULT 0' },
            { name: 'timely_payments', sql: 'ALTER TABLE order_users ADD COLUMN timely_payments INT DEFAULT 0' },
            { name: 'total_spent', sql: 'ALTER TABLE order_users ADD COLUMN total_spent DECIMAL(10,2) DEFAULT 0.00' },
            { name: 'created_at', sql: 'ALTER TABLE order_users ADD COLUMN created_at DATETIME DEFAULT CURRENT_TIMESTAMP' }
        ];

        for (const def of defs) {
            if (!has(def.name)) {
                try {
                    await db.execute(def.sql);
                    console.log(`✅ 已为 order_users 表新增列 ${def.name}`);
                } catch (e) {
                    console.warn(`⚠️ 为 order_users 表新增列 ${def.name} 失败:`, e.message);
                }
            }
        }
    } catch (e) {
        console.warn('⚠️ 检查/修复 order_users 表结构失败:', e.message);
    }
}

// ====== 校园地点表：广东药科大学云浮校区 ======

async function ensureCampusLocationsTable() {
    try {
        await db.execute(`
            CREATE TABLE IF NOT EXISTS campus_locations (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(100) NOT NULL,
                type VARCHAR(50) NOT NULL DEFAULT 'pickup_point',
                lat DECIMAL(10,6) NOT NULL,
                lng DECIMAL(10,6) NOT NULL,
                building_code VARCHAR(50) NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        `);

        // 如果表是空的，初始化几个核心点位（大致示意坐标即可，用于相对距离和演示）
        const [rows] = await db.execute('SELECT COUNT(*) AS cnt FROM campus_locations');
        const count = rows[0] && rows[0].cnt;
        if (!count) {
            await db.execute(
                `INSERT INTO campus_locations (name, type, lat, lng, building_code) VALUES
                 ('一期妈妈驿站', 'pickup_point', 23.123000, 112.987000, '一期宿舍3栋'),
                 ('一期丰巢',     'pickup_point', 23.123200, 112.986800, '西门'),
                 ('二期校园邮局', 'pickup_point', 23.125000, 112.989500, '二期操场旁')`
            );
            console.log('✅ 已初始化 campus_locations 核心点位');
        }
    } catch (e) {
        console.warn('⚠️ 初始化 campus_locations 表失败（不影响主流程）:', e.message);
    }
}

async function ensureCoreTables() {
    // order_users
    await db.execute(`
        CREATE TABLE IF NOT EXISTS order_users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            credit_score INT DEFAULT 100,
            total_orders INT DEFAULT 0,
            completed_orders INT DEFAULT 0,
            timely_payments INT DEFAULT 0,
            total_spent DECIMAL(10,2) DEFAULT 0.00,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    `);
    // 兼容已有数据库，补齐缺失的统计列
    await ensureOrderUsersColumns();
    // orders
    await db.execute(`
        CREATE TABLE IF NOT EXISTS orders (
            id INT AUTO_INCREMENT PRIMARY KEY,
            order_user_id INT NOT NULL,
            courier_user_id INT NULL,
            express_company VARCHAR(100) DEFAULT '',
            express_type VARCHAR(100) DEFAULT '',
            package_size VARCHAR(50) DEFAULT '',
            package_weight DECIMAL(10,2) DEFAULT 0,
            pickup_code VARCHAR(100) DEFAULT '',
            pickup_location VARCHAR(255) NOT NULL,
            delivery_address VARCHAR(255) NOT NULL,
            delivery_time DATETIME NULL,
            note TEXT,
            suggested_fee DECIMAL(10,2) NOT NULL DEFAULT 0.00,
            final_fee DECIMAL(10,2) NOT NULL DEFAULT 0.00,
            status INT NOT NULL DEFAULT 1,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            accepted_at DATETIME NULL,
            picked_up_at DATETIME NULL,
            delivered_at DATETIME NULL,
            completed_at DATETIME NULL
        )
    `);

    // 额外确保关键列存在（历史数据库可能缺失）
    await ensureOrderColumn('delivery_time', 'DATETIME NULL');
    await ensureOrderColumn('note', 'TEXT NULL');
    await ensureOrderColumn('suggested_fee', 'DECIMAL(10,2) NOT NULL DEFAULT 0.00');
    await ensureOrderColumn('final_fee', 'DECIMAL(10,2) NOT NULL DEFAULT 0.00');
    await ensureOrderColumn('pickup_code', 'VARCHAR(100) DEFAULT \'\'');
    await ensureOrderColumn('pickup_time', 'VARCHAR(100) DEFAULT \'\'');
    await ensureOrderColumn('urgency_level', 'VARCHAR(50) DEFAULT \'normal\'');
    await ensureOrderColumn('updated_at', 'DATETIME NULL');
    // 新增：确保坐标和真实距离字段存在
    await ensureOrderColumn('pickup_lat', 'DECIMAL(10,6) NULL');
    await ensureOrderColumn('pickup_lng', 'DECIMAL(10,6) NULL');
    await ensureOrderColumn('drop_lat', 'DECIMAL(10,6) NULL');
    await ensureOrderColumn('drop_lng', 'DECIMAL(10,6) NULL');
    await ensureOrderColumn('distance_m', 'INT NULL');
    orderTableColumnsCache = null;

    // 校园地点表（广东药科大学云浮校区）
    await ensureCampusLocationsTable();
}

async function ensureOrderColumn(columnName, definition) {
    try {
        const [cols] = await db.execute('SHOW COLUMNS FROM orders LIKE ?', [columnName]);
        if (cols.length === 0) {
            await db.execute(`ALTER TABLE orders ADD COLUMN ${columnName} ${definition}`);
            console.log(`✅ 已为 orders 表新增列 ${columnName}`);
            orderTableColumnsCache = null;
        }
    } catch (err) {
        console.warn(`⚠️ 检查/新增 orders.${columnName} 失败:`, err.message);
    }
}

async function ensureOrderColumns() {
    const columns = [
        { name: 'express_company', definition: "VARCHAR(100) DEFAULT ''" },
        { name: 'express_type', definition: "VARCHAR(100) DEFAULT ''" },
        { name: 'package_size', definition: "VARCHAR(50) DEFAULT ''" },
        { name: 'package_weight', definition: "DECIMAL(10,2) DEFAULT 0" },
        { name: 'pickup_code', definition: "VARCHAR(100) DEFAULT ''" },
        { name: 'pickup_location', definition: "VARCHAR(255) DEFAULT ''" },
        { name: 'delivery_address', definition: "VARCHAR(255) DEFAULT ''" },
        { name: 'delivery_time', definition: "DATETIME NULL" },
        { name: 'note', definition: "TEXT" },
        { name: 'suggested_fee', definition: "DECIMAL(10,2) DEFAULT 0" },
        { name: 'final_fee', definition: "DECIMAL(10,2) DEFAULT 0" },
        { name: 'status', definition: "INT NOT NULL DEFAULT 1" },
        { name: 'created_at', definition: "DATETIME DEFAULT CURRENT_TIMESTAMP" },
        { name: 'updated_at', definition: "DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" },
        { name: 'courier_user_id', definition: "INT NULL" },
        // 新增：坐标和真实距离字段
        { name: 'pickup_lat', definition: 'DECIMAL(10,6) NULL' },
        { name: 'pickup_lng', definition: 'DECIMAL(10,6) NULL' },
        { name: 'drop_lat', definition: 'DECIMAL(10,6) NULL' },
        { name: 'drop_lng', definition: 'DECIMAL(10,6) NULL' },
        { name: 'distance_m', definition: 'INT NULL' }
    ];

    for (const column of columns) {
        await db.execute(
            `ALTER TABLE orders ADD COLUMN IF NOT EXISTS ${column.name} ${column.definition}`
        ).catch(() => { });
    }
}

async function getOrderTableColumns() {
    if (orderTableColumnsCache) {
        return orderTableColumnsCache;
    }
    try {
        const [rows] = await db.execute('SHOW COLUMNS FROM orders');
        orderTableColumnsCache = new Set(rows.map((row) => row.Field));
    } catch (err) {
        console.warn('⚠️ 获取 orders 列信息失败:', err.message);
        orderTableColumnsCache = new Set();
    }
    return orderTableColumnsCache;
}

/**
 * 根据请求体解析并返回真实的 order_user_id（order_users 表主键）
 * 如果只提供了基础用户ID（users.id），会自动查询/创建对应的 order_users 记录
 */
async function resolveOrderUserId({ providedOrderUserId, providedUserId }) {
    if (providedOrderUserId) {
        return Number(providedOrderUserId);
    }

    if (!providedUserId) {
        return null;
    }

    const numericUserId = Number(providedUserId);
    if (Number.isNaN(numericUserId)) {
        return null;
    }

    // 查询是否已有下单者身份
    const [existingOrderUsers] = await db.execute(
        'SELECT id FROM order_users WHERE user_id = ? LIMIT 1',
        [numericUserId]
    );

    if (existingOrderUsers.length > 0) {
        return existingOrderUsers[0].id;
    }

    // 自动创建最小化记录
    const [result] = await db.execute(
        'INSERT INTO order_users (user_id, credit_score, total_orders, completed_orders) VALUES (?, 100, 0, 0)',
        [numericUserId]
    );
    return result.insertId;
}

function formatDateTime(dateInput) {
    const date = dateInput instanceof Date ? dateInput : new Date(dateInput || Date.now());
    const pad = (val) => String(val).padStart(2, '0');
    const year = date.getFullYear();
    const month = pad(date.getMonth() + 1);
    const day = pad(date.getDate());
    const hour = pad(date.getHours());
    const minute = pad(date.getMinutes());
    const second = pad(date.getSeconds());
    return `${year}-${month}-${day} ${hour}:${minute}:${second}`;
}

// ====== 校园坐标与距离计算辅助函数 ======

// 简单的楼栋/区域到经纬度映射（广东药科大学云浮校区）
// 注意：这里使用的是用户提供的实际坐标，便于更真实地估算距离
const campusLocations = [
    // 二期校园邮局
    { keyword: '二期校园邮局', lat: 23.03244068608438, lng: 112.18414064374304 },

    // 二期宿舍 8-13 栋一带（以 11 栋为代表点）
    { keyword: '11栋', lat: 23.032168269626208, lng: 112.18581805296776 },
    { keyword: '8栋', lat: 23.032168269626208, lng: 112.18581805296776 },
    { keyword: '9栋', lat: 23.032168269626208, lng: 112.18581805296776 },
    { keyword: '10栋', lat: 23.032168269626208, lng: 112.18581805296776 },
    { keyword: '12栋', lat: 23.032168269626208, lng: 112.18581805296776 },
    { keyword: '13栋', lat: 23.032168269626208, lng: 112.18581805296776 },

    // 一期宿舍 1-7 栋一带
    { keyword: '1栋', lat: 23.026344332102518, lng: 112.18556807654068 },
    { keyword: '2栋', lat: 23.026344332102518, lng: 112.18556807654068 },
    { keyword: '3栋', lat: 23.026344332102518, lng: 112.18556807654068 },
    { keyword: '4栋', lat: 23.026344332102518, lng: 112.18556807654068 },
    { keyword: '5栋', lat: 23.026344332102518, lng: 112.18556807654068 },
    { keyword: '6栋', lat: 23.026344332102518, lng: 112.18556807654068 },
    { keyword: '7栋', lat: 23.026344332102518, lng: 112.18556807654068 },

    // 食堂区域
    { keyword: '饭堂', lat: 23.030508309535033, lng: 112.18748495285911 },
    { keyword: '食堂', lat: 23.030508309535033, lng: 112.18748495285911 },

    // 田径场
    { keyword: '田径场', lat: 23.032251201686847, lng: 112.18280219251722 },

    // 西门一带（妈妈驿站 / 丰巢）
    { keyword: '西门', lat: 23.026414506588914, lng: 112.17873239158416 },
    { keyword: '妈妈驿站', lat: 23.026414506588914, lng: 112.17873239158416 },
    { keyword: '丰巢', lat: 23.026414506588914, lng: 112.17873239158416 },
    { keyword: '驿站', lat: 23.026414506588914, lng: 112.17873239158416 }
];

function getLocationCoords(text) {
    if (!text) return null;
    const str = String(text);
    for (const loc of campusLocations) {
        if (str.includes(loc.keyword)) {
            return { lat: loc.lat, lng: loc.lng };
        }
    }
    return null;
}

// 哈弗辛公式计算两点间球面距离（米）
function haversineDistanceMeters(lat1, lng1, lat2, lng2) {
    const toRad = (d) => (d * Math.PI) / 180;
    const R = 6371000; // 地球半径（米）
    const dLat = toRad(lat2 - lat1);
    const dLng = toRad(lng2 - lng1);
    const a =
        Math.sin(dLat / 2) * Math.sin(dLat / 2) +
        Math.cos(toRad(lat1)) * Math.cos(toRad(lat2)) *
        Math.sin(dLng / 2) * Math.sin(dLng / 2);
    const c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a));
    return Math.round(R * c);
}

async function resolveCourierUserId({ courierUserId, userId }) {
    if (courierUserId) {
        return Number(courierUserId);
    }

    if (!userId) {
        return null;
    }

    const numeric = Number(userId);
    if (Number.isNaN(numeric)) {
        return null;
    }

    const [rows] = await db.execute(
        'SELECT id FROM courier_users WHERE user_id = ? LIMIT 1',
        [numeric]
    );

    if (rows.length > 0) {
        return rows[0].id;
    }

    // 如果当前用户还没有代拿员档案，自动创建一个最小可用的 courier_users 记录
    try {
        const [result] = await db.execute(
            'INSERT INTO courier_users (user_id, credit_score) VALUES (?, 100)',
            [numeric]
        );
        console.log('✅ 已为用户自动创建代拿员身份:', numeric, 'courier_user_id:', result.insertId);
        return result.insertId;
    } catch (e) {
        console.warn('⚠️ 自动创建代拿员身份失败:', e.message);
        return null;
    }
}

// 创建新订单 - 支持前端camelCase字段格式
router.post('/', async (req, res) => {
    const body = req.body || {};
    console.log('📝 接收到创建订单请求:', JSON.stringify(body));

    try {
        await ensureCoreTables().catch((e) => {
            console.warn('⚠️ ensureCoreTables 失败（继续）：', e.message);
        });
        await ensureOrderColumns().catch((e) => {
            console.warn('⚠️ ensureOrderColumns 失败（继续）：', e.message);
        });

        const orderUserId = await resolveOrderUserId({
            providedOrderUserId: body.order_user_id || body.orderUserId || body.order_user,
            providedUserId: body.userId || body.user_id
        });

        if (!orderUserId) {
            return res.status(400).json({
                success: false,
                message: '缺少用户身份，请提供 userId 或 order_user_id',
                errorCode: 4001
            });
        }

        const pickupLocation = body.pickup_location || body.pickupLocation;
        const deliveryAddress = body.delivery_address || body.deliveryAddress || body.address;

        if (!pickupLocation || !deliveryAddress) {
            return res.status(400).json({
                success: false,
                message: '取件地点与送达地点为必填项',
                errorCode: 4002
            });
        }

        const expressCompany = body.express_company || body.expressCompany || '其他';
        const expressType = body.express_type || body.expressType || (Array.isArray(body.itemTypes) ? body.itemTypes.join(',') : body.itemTypes || '日用品');
        const packageSize = body.package_size || body.packageSize || 'M';
        const packageWeight = parseFloat(body.package_weight || body.packageWeight || body.estimatedWeight || 0) || 0;
        const pickupCode = body.pickup_code || body.pickupCode || '';
        const note = body.note || body.description || body.remarks || '';
        const suggestedFeeInput = Number(body.suggested_fee || body.suggestedFee || body.estimatedPrice || body.customPrice || 0);
        const suggestedFee = suggestedFeeInput > 0 ? suggestedFeeInput : calculateSuggestedFee(expressCompany, pickupLocation, deliveryAddress);
        const deliveryTimeInput = body.delivery_time || body.deliveryTime || body.expectedTime;
        const deliveryTime = formatDateTime(deliveryTimeInput);
        // 确保pickup_time为合法时间：优先使用前端传的pickup_time，其次复用delivery_time，最后使用当前时间
        const pickupTimeInput = body.pickup_time || body.pickupTime || deliveryTimeInput || new Date();
        const pickupTime = formatDateTime(pickupTimeInput);
        const statusValue = 1; // 待接单

        // 计算坐标和真实距离（如果能匹配到校园坐标）
        const pickupCoords = getLocationCoords(pickupLocation);
        const dropCoords = getLocationCoords(deliveryAddress);
        let distanceM = null;
        if (pickupCoords && dropCoords) {
            try {
                distanceM = haversineDistanceMeters(
                    pickupCoords.lat,
                    pickupCoords.lng,
                    dropCoords.lat,
                    dropCoords.lng
                );
            } catch (e) {
                console.warn('计算距离失败（非致命）:', e.message);
            }
        }

        const orderColumns = await getOrderTableColumns();
        const columnValuePairs = [];
        const pushColumn = (name, value) => {
            if (!name) return;
            if (orderColumns.has(name) && value !== undefined && value !== null) {
                columnValuePairs.push({ name, value });
            }
        };

        pushColumn('order_user_id', orderUserId);
        pushColumn('pickup_location', pickupLocation);
        pushColumn('delivery_address', deliveryAddress);
        pushColumn('express_company', expressCompany);
        pushColumn('express_type', expressType);
        pushColumn('package_size', packageSize);
        pushColumn('package_weight', packageWeight);
        pushColumn('pickup_code', pickupCode);
        pushColumn('delivery_time', deliveryTime);
        pushColumn('note', note);
        pushColumn('suggested_fee', suggestedFee);
        pushColumn('final_fee', suggestedFee);
        pushColumn('status', statusValue);
        pushColumn('urgency_level', body.urgency_level || body.urgencyLevel || 'normal');
        pushColumn('pickup_time', pickupTime);
        if (pickupCoords) {
            pushColumn('pickup_lat', pickupCoords.lat);
            pushColumn('pickup_lng', pickupCoords.lng);
        }
        if (dropCoords) {
            pushColumn('drop_lat', dropCoords.lat);
            pushColumn('drop_lng', dropCoords.lng);
        }
        if (distanceM !== null) {
            pushColumn('distance_m', distanceM);
        }
        pushColumn('updated_at', formatDateTime(new Date()));

        if (columnValuePairs.length === 0) {
            return res.status(500).json({
                success: false,
                message: '订单表结构异常：无法写入任何字段'
            });
        }

        const columnsSql = columnValuePairs.map((c) => c.name).join(', ');
        const placeholdersSql = columnValuePairs.map(() => '?').join(', ');
        const values = columnValuePairs.map((c) => c.value);

        const sql = `INSERT INTO orders (${columnsSql}) VALUES (${placeholdersSql})`;

        const [result] = await db.execute(sql, values);
        const orderId = result.insertId;

        db.execute(
            'UPDATE order_users SET total_orders = COALESCE(total_orders, 0) + 1, total_spent = COALESCE(total_spent, 0) + ? WHERE id = ?',
            [suggestedFee, orderUserId]
        ).catch((err) => console.warn('⚠️ 更新下单者统计失败:', err.message));

        let matchingResult = { matchedCouriers: [] };
        try {
            matchingResult = await intelligentOrderMatching({
                id: orderId,
                pickup_location: pickupLocation,
                delivery_address: deliveryAddress
            });
        } catch (matchingErr) {
            console.warn('⚠️ 智能匹配失败（不影响下单）:', matchingErr.message);
        }

        return res.json({
            success: true,
            message: '订单创建成功',
            data: {
                orderId,
                status: statusValue,
                fee: suggestedFee,
                matchingInfo: matchingResult.matchedCouriers || [],
                deliveryTime
            }
        });
    } catch (error) {
        console.error('创建订单错误:', error);
        res.status(500).json({
            success: false,
            message: '创建订单失败',
            error: error.message,
            errorCode: error.code || 'SERVER_ERROR',
            sqlMessage: error.sqlMessage || null,
            debug: process.env.NODE_ENV !== 'production' ? {
                sql: error.sql || null,
                requestBody: req.body
            } : undefined
        });
    }
});

// 获取所有校园地点（主要用于前端调试或下拉列表）
router.get('/campus/locations', async (req, res) => {
    try {
        const [rows] = await db.execute('SELECT * FROM campus_locations ORDER BY id ASC');
        res.json({
            success: true,
            data: rows
        });
    } catch (error) {
        console.error('❌ 获取校园地点失败:', error);
        res.status(500).json({
            success: false,
            message: '获取校园地点失败'
        });
    }
});

// 根据当前坐标获取最近的校园取件点
router.get('/campus/nearest', async (req, res) => {
    try {
        const lat = Number(req.query.lat);
        const lng = Number(req.query.lng);
        const type = req.query.type || 'pickup_point';

        if (!lat || !lng || Number.isNaN(lat) || Number.isNaN(lng)) {
            return res.status(400).json({
                success: false,
                message: '缺少或坐标格式错误'
            });
        }

        const [rows] = await db.execute(
            'SELECT * FROM campus_locations WHERE type = ?', [type]
        );

        if (!rows.length) {
            return res.json({
                success: true,
                data: null
            });
        }

        let best = null;
        let bestDist = Number.MAX_SAFE_INTEGER;
        for (const loc of rows) {
            const d = haversineDistanceMeters(lat, lng, Number(loc.lat), Number(loc.lng));
            if (d < bestDist) {
                bestDist = d;
                best = { ...loc, distance_m: d };
            }
        }

        res.json({
            success: true,
            data: best
        });
    } catch (error) {
        console.error('❌ 获取最近校园地点失败:', error);
        res.status(500).json({
            success: false,
            message: '获取最近校园地点失败'
        });
    }
});

// 添加费用计算函数（在文件末尾，module.exports之前添加）
function calculateSuggestedFee(expressCompany, pickupLocation, deliveryAddress) {
    // 简单费用计算逻辑
    let baseFee = 1.00; // 基础费用



    // 根据距离调整（简化逻辑）
    const distance = estimateDistance(pickupLocation, deliveryAddress);
    if (distance > 1000) {
        baseFee += 1.00;
    }

    return parseFloat(baseFee.toFixed(2));
}

// 估算距离（简化版）
function estimateDistance(pickup, delivery) {
    // 优先尝试根据校园坐标估算真实距离
    try {
        const pc = getLocationCoords(pickup);
        const dc = getLocationCoords(delivery);
        if (pc && dc) {
            return haversineDistanceMeters(pc.lat, pc.lng, dc.lat, dc.lng);
        }
    } catch (e) {
        console.warn('estimateDistance 计算失败，回退默认值:', e.message);
    }

    // 实际项目中可以集成地图API计算距离，这里保留一个安全的默认值
    return 500; // 默认500米
}

function computeSmartScoreForOrder(order, courierPrefs) {
    const distanceM = Number(order.distance_m || 0) || 0;
    const fee = Number(order.final_fee || order.suggested_fee || order.fee || 0) || 0;
    const creditRaw = Number(order.orderer_credit || order.credit_score || 0) || 0;
    const serviceRange = courierPrefs && courierPrefs.service_range ? String(courierPrefs.service_range) : '';
    const serviceTime = courierPrefs && courierPrefs.service_time ? String(courierPrefs.service_time) : '';

    let distanceScore = 60;
    if (distanceM > 0) {
        if (distanceM <= 200) distanceScore = 100;
        else if (distanceM <= 800) distanceScore = 85;
        else if (distanceM <= 1500) distanceScore = 70;
        else distanceScore = 55;
    }

    let priceScore = 50;
    if (fee > 0) {
        if (fee < 2) priceScore = 60;
        else if (fee < 4) priceScore = 75;
        else priceScore = 90;
    }

    let creditScore = creditRaw;
    if (creditScore < 0) creditScore = 0;
    if (creditScore > 100) creditScore = 100;

    let rangeScore = 70;
    if (serviceRange) {
        const text = (order.delivery_address || order.pickup_location || '') + '';
        rangeScore = text && serviceRange && text.includes(serviceRange) ? 90 : 70;
    }

    let timeScore = 70;
    if (serviceTime) {
        const baseTimeStr = (order.delivery_time || order.expected_time || order.pickup_time || order.created_at || '') + '';
        const match = isTimeRoughlyInService(baseTimeStr, serviceTime);
        timeScore = match ? 90 : 70;
    }

    // 权重优先级：距离 > 价格 > (服务范围+时间) > 信用值
    const wDistance = 0.45; // 距离仍然是最重要的
    const wPrice = 0.25;    // 价格其次
    const wRange = 0.12;    // 服务范围匹配
    const wTime = 0.10;     // 常接单时间匹配
    const wCredit = 0.08;   // 下单者信用

    const score =
        wDistance * distanceScore +
        wPrice * priceScore +
        wCredit * creditScore +
        wRange * rangeScore +
        wTime * timeScore;

    return Math.round(score);
}

function isTimeRoughlyInService(orderTime, serviceTime) {
    if (!orderTime || !serviceTime) return false;
    const t = new Date(orderTime);
    if (Number.isNaN(t.getTime())) return false;
    const minutes = t.getHours() * 60 + t.getMinutes();

    const segments = String(serviceTime)
        .split(',')
        .map((s) => s.trim())
        .filter(Boolean);

    for (const seg of segments) {
        const parts = seg.split('-').map((s) => s.trim());
        if (parts.length !== 2) continue;
        const start = parseHmToMinutes(parts[0]);
        const end = parseHmToMinutes(parts[1]);
        if (start == null || end == null) continue;
        if (minutes >= start && minutes <= end) return true;
    }
    return false;
}

function parseHmToMinutes(hm) {
    const m = String(hm).match(/^(\d{1,2}):(\d{2})$/);
    if (!m) return null;
    const h = Number(m[1]);
    const min = Number(m[2]);
    if (Number.isNaN(h) || Number.isNaN(min)) return null;
    return h * 60 + min;
}

// 获取订单列表
router.get('/', async (req, res) => {
    const { user_id, user_type, status, keyword = '', startDate, endDate, courier } = req.query || {};

    try {
        let sql = `
      SELECT o.*, u.nickname as user_nickname, u.phone as user_phone,
             cu.nickname as courier_nickname, cu.phone as courier_phone
      FROM orders o
      LEFT JOIN order_users ou ON o.order_user_id = ou.id
      LEFT JOIN users u ON ou.user_id = u.id
      LEFT JOIN courier_users cou ON o.courier_user_id = cou.id
      LEFT JOIN users cu ON cou.user_id = cu.id
      WHERE 1=1
    `;
        let params = [];

        // 根据用户类型筛选订单
        if (user_type === 'order_user' && user_id) {
            sql += ' AND o.order_user_id IN (SELECT id FROM order_users WHERE user_id = ?)';
            params.push(user_id);
        } else if (user_type === 'courier_user' && user_id) {
            sql += ' AND o.courier_user_id IN (SELECT id FROM courier_users WHERE user_id = ?)';
            params.push(user_id);
        }

        // 根据状态筛选（同时支持中文文本和数字码）
        if (status) {
            let statusValue = status;
            if (typeof status === 'string') {
                const map = {
                    '待接单': 1,
                    '配送中': 2,
                    '已完成': 5,
                    '已取消': 4
                };
                if (map[status]) {
                    statusValue = map[status];
                }
            }
            sql += ' AND o.status = ?';
            params.push(statusValue);
        }

        // 关键字搜索：订单ID、下单者昵称/手机号、地址
        if (keyword) {
            const like = `%${keyword}%`;
            sql += ' AND (CAST(o.id AS CHAR) LIKE ? OR u.nickname LIKE ? OR u.phone LIKE ? OR o.pickup_location LIKE ? OR o.delivery_address LIKE ?)';
            params.push(like, like, like, like, like);
        }

        // 日期范围：按创建时间
        if (startDate) {
            sql += ' AND DATE(o.created_at) >= ?';
            params.push(startDate);
        }
        if (endDate) {
            sql += ' AND DATE(o.created_at) <= ?';
            params.push(endDate);
        }

        // 代拿员筛选：昵称或手机号
        if (courier) {
            const like = `%${courier}%`;
            sql += ' AND (cu.nickname LIKE ? OR cu.phone LIKE ?)';
            params.push(like, like);
        }

        sql += ' ORDER BY o.created_at DESC';

        const [orders] = await db.execute(sql, params);

        res.json({
            success: true,
            data: orders,
            total: orders.length
        });

    } catch (error) {
        console.error('获取订单列表错误:', error);
        res.status(500).json({
            success: false,
            message: '获取订单列表失败',
            error: error.message
        });
    }
});


async function acceptOrderHandler(req, res) {
    const orderId = req.params.id;
    const courierUserId = await resolveCourierUserId({
        courierUserId: req.body?.courier_user_id || req.body?.courierUserId,
        userId: req.body?.userId || req.body?.courierId || req.body?.courier_user
    });

    try {
        if (!courierUserId) {
            return res.status(400).json({
                success: false,
                message: '缺少有效的代拿员身份'
            });
        }

        const [orders] = await db.execute(
            'SELECT * FROM orders WHERE id = ? AND status = 1',
            [orderId]
        );

        if (orders.length === 0) {
            return res.status(404).json({
                success: false,
                message: '订单不存在或状态不可接单'
            });
        }

        const [couriers] = await db.execute(
            'SELECT * FROM courier_users WHERE id = ?',
            [courierUserId]
        );

        if (couriers.length === 0) {
            return res.status(400).json({
                success: false,
                message: '代拿员不存在'
            });
        }

        await db.execute(
            'UPDATE orders SET courier_user_id = ?, status = 2, accepted_at = NOW() WHERE id = ?',
            [courierUserId, orderId]
        );

        await db.execute(
            'UPDATE courier_users SET total_orders = COALESCE(total_orders, 0) + 1 WHERE id = ?',
            [courierUserId]
        );

        res.json({
            success: true,
            message: '接单成功',
            data: {
                order_id: orderId,
                courier_user_id: courierUserId,
                status: 2,
                accepted_at: new Date()
            }
        });

    } catch (error) {
        console.error('接单错误:', error);
        res.status(500).json({
            success: false,
            message: '接单失败',
            error: error.message
        });
    }
}

router.post('/:id/accept', acceptOrderHandler);
router.put('/:id/accept', acceptOrderHandler);

// 更新订单状态
router.put('/:id/status', async (req, res) => {
    const orderId = req.params.id;
    const { status } = req.body;

    try {
        // 1. 验证状态值
        if (![2, 3, 4, 5, 6].includes(status)) {
            return res.status(400).json({
                success: false,
                message: '状态值无效'
            });
        }

        // 2. 检查订单是否存在
        const [orders] = await db.execute(
            'SELECT * FROM orders WHERE id = ?',
            [orderId]
        );

        if (orders.length === 0) {
            return res.status(404).json({
                success: false,
                message: '订单不存在'
            });
        }

        // 3. 根据状态更新相应的时间字段
        let updateSql = 'UPDATE orders SET status = ?';
        const updateParams = [status, orderId];

        switch (status) {
            case 3: // 已取件
                updateSql += ', picked_up_at = NOW()';
                break;
            case 4: // 已送达
                updateSql += ', delivered_at = NOW()';
                break;
            case 5: // 已完成
                updateSql += ', completed_at = NOW()';
                // 更新代拿员完成订单数
                await db.execute(
                    'UPDATE courier_users SET completed_orders = completed_orders + 1 WHERE id = ?',
                    [orders[0].courier_user_id]
                );
                break;
        }

        updateSql += ' WHERE id = ?';

        // 4. 执行更新
        await db.execute(updateSql, updateParams);

        res.json({
            success: true,
            message: '订单状态更新成功',
            data: {
                order_id: orderId,
                status,
                updated_at: new Date()
            }
        });

    } catch (error) {
        console.error('更新订单状态错误:', error);
        res.status(500).json({
            success: false,
            message: '更新订单状态失败',
            error: error.message
        });
    }
});

// 获取用户订单 - 新增接口
router.get('/user/:userId', async (req, res) => {
    try {
        const { userId } = req.params;
        console.log(`📦 获取用户 ${userId} 的订单`);

        const [orders] = await db.execute(`
            SELECT o.*, 
                   u.nickname as user_nickname,
                   cu.nickname as courier_nickname
            FROM orders o
            LEFT JOIN order_users ou ON o.order_user_id = ou.id
            LEFT JOIN users u ON ou.user_id = u.id
            LEFT JOIN courier_users cou ON o.courier_user_id = cou.id
            LEFT JOIN users cu ON cou.user_id = cu.id
            WHERE ou.user_id = ?
            ORDER BY o.created_at DESC
        `, [userId]);

        res.json({
            success: true,
            data: orders
        });

    } catch (error) {
        console.error('❌ 获取用户订单失败:', error);
        res.status(500).json({
            success: false,
            message: '获取订单失败'
        });
    }
});

// 获取代拿员订单 - 新增接口
router.get('/courier/:userId', async (req, res) => {
    try {
        const { userId } = req.params;
        console.log(`🚴 获取代拿员 ${userId} 的订单`);

        const [orders] = await db.execute(`
            SELECT o.*, 
                   u.nickname as user_nickname,
                   ou.user_id as order_user_info,
                   ou.credit_score as orderer_credit
            FROM orders o
            LEFT JOIN order_users ou ON o.order_user_id = ou.id
            LEFT JOIN users u ON ou.user_id = u.id
            WHERE o.courier_user_id IN (
                SELECT id FROM courier_users WHERE user_id = ?
            )
            ORDER BY o.created_at DESC
        `, [userId]);

        res.json({
            success: true,
            data: orders
        });

    } catch (error) {
        console.error('❌ 获取代拿员订单失败:', error);
        res.status(500).json({
            success: false,
            message: '获取订单失败'
        });
    }
});

router.get('/available', async (req, res) => {
    try {
        const userLat = Number(req.query.lat);
        const userLng = Number(req.query.lng);
        const hasUserLocation = !Number.isNaN(userLat) && !Number.isNaN(userLng);

        const courierUserId = req.query.courierId ? Number(req.query.courierId) : null;
        let courierPrefs = null;
        if (courierUserId && !Number.isNaN(courierUserId)) {
            try {
                const [rows] = await db.execute(
                    'SELECT service_range, service_time FROM courier_users WHERE user_id = ? LIMIT 1',
                    [courierUserId]
                );
                if (rows && rows.length > 0) {
                    courierPrefs = rows[0];
                }
            } catch (e) {
                console.warn('获取代拿员偏好失败（非致命）:', e.message);
            }
        }

        console.log(`🛒 获取可接订单（不分页），lat: ${userLat}, lng: ${userLng}, courierUserId: ${courierUserId}`);

        // 基础过滤条件：仅返回待接单且尚未指派代拿员的订单
        let whereSql = 'WHERE o.status = 1 AND o.courier_user_id IS NULL';

        // 如果传入了 courierId，则排除当前用户自己发布的订单，防止接到自己的单
        if (courierUserId && !Number.isNaN(courierUserId)) {
            whereSql += ` AND o.order_user_id NOT IN (SELECT id FROM order_users WHERE user_id = ${courierUserId})`;
        }

        const sql = `SELECT o.*, u.nickname AS user_nickname, ou.credit_score AS orderer_credit
                 FROM orders o
                 LEFT JOIN order_users ou ON o.order_user_id = ou.id
                 LEFT JOIN users u ON ou.user_id = u.id
                 ${whereSql}`;

        const [ordersRaw] = await db.execute(sql);

        let orders = ordersRaw;

        // 如果前端提供了当前位置，并且订单有取件点坐标，则计算代拿员到取件点的真实距离并按距离升序排序
        if (hasUserLocation) {
            orders = ordersRaw.map((o) => {
                let courierDistance = null;
                if (o.pickup_lat != null && o.pickup_lng != null) {
                    try {
                        courierDistance = haversineDistanceMeters(
                            Number(userLat),
                            Number(userLng),
                            Number(o.pickup_lat),
                            Number(o.pickup_lng)
                        );
                    } catch (e) {
                        console.warn('计算代拿员距离失败（非致命）:', e.message);
                    }
                }
                return {
                    ...o,
                    courier_distance_m: courierDistance
                };
            });

            orders.sort((a, b) => {
                const da = typeof a.courier_distance_m === 'number' ? a.courier_distance_m : Number.MAX_SAFE_INTEGER;
                const dbVal = typeof b.courier_distance_m === 'number' ? b.courier_distance_m : Number.MAX_SAFE_INTEGER;
                if (da !== dbVal) return da - dbVal;
                // 距离相同时，按创建时间倒序
                const ta = a.created_at ? new Date(a.created_at).getTime() : 0;
                const tb = b.created_at ? new Date(b.created_at).getTime() : 0;
                return tb - ta;
            });
        } else {
            // 未提供当前位置时，保持原有“最新发布”排序
            orders.sort((a, b) => {
                const ta = a.created_at ? new Date(a.created_at).getTime() : 0;
                const tb = b.created_at ? new Date(b.created_at).getTime() : 0;
                return tb - ta;
            });
        }

        // 为每条订单计算智能推荐分数（主要考虑距离、价格和下单者信用，结合代拿员偏好）
        if (courierPrefs) {
            orders = orders.map((o) => {
                const smartScore = computeSmartScoreForOrder(o, courierPrefs);
                return {
                    ...o,
                    smart_score: smartScore
                };
            });
        }

        res.json({
            success: true,
            data: {
                orders,
                total: orders.length,
                hasMore: false
            }
        });
    } catch (error) {
        console.error('❌ 获取可接订单失败:', error);
        res.json({
            success: true,
            data: {
                orders: [],
                total: 0,
                hasMore: false
            }
        });
    }
});

// 获取订单统计 - 新增接口
router.get('/stats/:userId', async (req, res) => {
    try {
        const { userId } = req.params;

        const [stats] = await db.execute(`
        SELECT 
            COUNT(*) as totalOrders,
            COUNT(CASE WHEN status IN (4,5,6) THEN 1 END) as completedOrders,
            COUNT(CASE WHEN status IN (1,2,3) THEN 1 END) as pendingOrders
        FROM orders 
        WHERE order_user_id IN (
            SELECT id FROM order_users WHERE user_id = ?
        )
    `, [userId]);

        res.json({
            success: true,
            data: stats[0] || { totalOrders: 0, completedOrders: 0, pendingOrders: 0 }
        });

    } catch (error) {
        console.error('❌ 获取订单统计失败:', error);
        res.status(500).json({
            success: false,
            message: '获取统计失败'
        });
    }
});

// 确认取件 - 新增接口
router.post('/:id/pickup', async (req, res) => {
    try {
        const orderId = req.params.id;

        // 更新订单状态为已取件
        await db.execute(
            'UPDATE orders SET status = 3, picked_up_at = NOW() WHERE id = ?',
            [orderId]
        );

        res.json({
            success: true,
            message: '取件确认成功'
        });

    } catch (error) {
        console.error('❌ 确认取件失败:', error);
        res.status(500).json({
            success: false,
            message: '确认取件失败'
        });
    }
});

// 通知送达 - 新增接口
router.post('/:id/deliver', async (req, res) => {
    try {
        const orderId = req.params.id;

        // 更新订单状态为送达中
        await db.execute(
            'UPDATE orders SET status = 4, delivered_at = NOW() WHERE id = ?',
            [orderId]
        );

        res.json({
            success: true,
            message: '已通知用户取件'
        });

    } catch (error) {
        console.error('❌ 通知送达失败:', error);
        res.status(500).json({
            success: false,
            message: '通知送达失败'
        });
    }
});

// 获取订单详情（需放在最后，避免与其他路由冲突）
router.get('/:id', async (req, res) => {
    const orderId = req.params.id;

    try {
        const [orders] = await db.execute(
            `SELECT o.*, 
                u.nickname as user_nickname, 
                u.phone as user_phone,
                ou.user_id AS orderer_user_id,
                cu.id AS courier_user,
                cu.nickname as courier_nickname, 
                cu.phone as courier_phone
         FROM orders o
         LEFT JOIN order_users ou ON o.order_user_id = ou.id
         LEFT JOIN users u ON ou.user_id = u.id
         LEFT JOIN courier_users cou ON o.courier_user_id = cou.id
         LEFT JOIN users cu ON cou.user_id = cu.id
         WHERE o.id = ?`,
            [orderId]
        );

        if (orders.length === 0) {
            return res.status(404).json({
                success: false,
                message: '订单不存在'
            });
        }

        let order = orders[0];

        // 如果历史订单没有坐标/距离，尝试补全一次
        try {
            if (!order.distance_m || !order.pickup_lat || !order.drop_lat) {
                const pCoords = getLocationCoords(order.pickup_location);
                const dCoords = getLocationCoords(order.delivery_address);

                if (pCoords && dCoords) {
                    const dist = haversineDistanceMeters(
                        pCoords.lat,
                        pCoords.lng,
                        dCoords.lat,
                        dCoords.lng
                    );

                    await db.execute(
                        `UPDATE orders 
                     SET pickup_lat = ?, pickup_lng = ?, 
                         drop_lat = ?, drop_lng = ?, 
                         distance_m = ?
                     WHERE id = ?`,
                        [pCoords.lat, pCoords.lng, dCoords.lat, dCoords.lng, dist, orderId]
                    );

                    order = {
                        ...order,
                        pickup_lat: pCoords.lat,
                        pickup_lng: pCoords.lng,
                        drop_lat: dCoords.lat,
                        drop_lng: dCoords.lng,
                        distance_m: dist
                    };
                }
            }
        } catch (e) {
            console.warn('补全订单坐标/距离失败（非致命）:', e.message);
        }

        res.json({
            success: true,
            data: order
        });

    } catch (error) {
        console.error('获取订单详情错误:', error);
        res.status(500).json({
            success: false,
            message: '获取订单详情失败',
            error: error.message
        });
    }
});

module.exports = router;